﻿using System;
using System.Collections.Generic;
using System.Data;
using Pku.BookStore.Model;
using System.Data.Common;

namespace Pku.BookStore.DAL
{
    /// <summary>
    /// 数据访问类
    /// </summary>
    public class ProductDAL : BaseDAL
    {
        string commandText = string.Empty;

        /// <summary>
        /// 增加图书信息
        /// </summary>
        /// <param name="entity"></param>
        public int Add(ProductModel entity)
        {
            commandText = @"INSERT INTO Product(PublisherId,Author,ISBN,SaleNum,BookName,Image,Recommend,Introduction,Directory,PublishDate,CategoryID,Price,TruePrice,AddDate,isSpecial,CostPrice)
VALUES(@PublisherId,@Author,@ISBN,@SaleNum,@BookName,@Image,@Recommend,@Introduction,@Directory,@PublishDate,@CategoryID,@Price,@TruePrice,getdate(),@isSpecial,@CostPrice);
SET  @BookID=scope_identity();
";
            var command = DbMain.GetSqlStringCommand(commandText);
            DbMain.AddInParameter(command, "PublisherId", DbType.Int32, entity.PublisherId);
            DbMain.AddInParameter(command, "Author", DbType.String, entity.Author);
            DbMain.AddInParameter(command, "ISBN", DbType.AnsiString, entity.ISBN);
            DbMain.AddInParameter(command, "SaleNum", DbType.Int32, entity.SaleNum);
            DbMain.AddInParameter(command, "BookName", DbType.String, entity.BookName);
            DbMain.AddInParameter(command, "Image", DbType.AnsiString, entity.Image);
            DbMain.AddInParameter(command, "Recommend", DbType.String, entity.Recommend);
            DbMain.AddInParameter(command, "Introduction", DbType.String, entity.Introduction);
            DbMain.AddInParameter(command, "Directory", DbType.String, entity.Directory);
            DbMain.AddInParameter(command, "PublishDate", DbType.DateTime, entity.PublishDate);
            DbMain.AddInParameter(command, "CategoryID", DbType.Int32, entity.CategoryID);
            DbMain.AddInParameter(command, "Price", DbType.Decimal, entity.Price);
            DbMain.AddInParameter(command, "TruePrice", DbType.Decimal, entity.TruePrice);
            DbMain.AddInParameter(command, "isSpecial", DbType.Int32, entity.isSpecial);
            DbMain.AddInParameter(command, "CostPrice", DbType.Decimal, entity.CostPrice);
            DbMain.AddOutParameter(command, "BookID", DbType.Int32, 10);
            DbMain.ExecuteNonQuery(command);

            return (int)DbMain.GetParameterValue(command, "BookID");

        }

        /// <summary>
        /// 修改图书信息
        /// </summary>
        /// <param name="entity"></param>
        public bool Modify(ProductModel entity)
        {
            commandText = @"UPDATE Product SET PublisherId=@PublisherId,Author=@Author,ISBN=@ISBN,SaleNum=@SaleNum,BookName=@BookName,Recommend=@Recommend,Introduction=@Introduction,Directory=@Directory,PublishDate=@PublishDate,CategoryID=@CategoryID,Price=@Price,TruePrice=@TruePrice,isSpecial=@isSpecial,CostPrice=@CostPrice WHERE BookID=@BookID";
            var command = DbMain.GetSqlStringCommand(commandText);
            DbMain.AddInParameter(command, "BookID", DbType.Int32, entity.BookID);
            DbMain.AddInParameter(command, "PublisherId", DbType.Int32, entity.PublisherId);
            DbMain.AddInParameter(command, "Author", DbType.String, entity.Author);
            DbMain.AddInParameter(command, "ISBN", DbType.AnsiString, entity.ISBN);
            DbMain.AddInParameter(command, "SaleNum", DbType.Int32, entity.SaleNum);
            DbMain.AddInParameter(command, "BookName", DbType.String, entity.BookName);
            DbMain.AddInParameter(command, "Recommend", DbType.String, entity.Recommend);
            DbMain.AddInParameter(command, "Introduction", DbType.String, entity.Introduction);
            DbMain.AddInParameter(command, "Directory", DbType.String, entity.Directory);
            DbMain.AddInParameter(command, "PublishDate", DbType.DateTime, entity.PublishDate);
            DbMain.AddInParameter(command, "CategoryID", DbType.Int32, entity.CategoryID);
            DbMain.AddInParameter(command, "Price", DbType.Decimal, entity.Price);
            DbMain.AddInParameter(command, "TruePrice", DbType.Decimal, entity.TruePrice);
            DbMain.AddInParameter(command, "isSpecial", DbType.Int32, entity.isSpecial);
            DbMain.AddInParameter(command, "CostPrice", DbType.Decimal, entity.CostPrice);

            return (int)DbMain.ExecuteNonQuery(command) == 1;
        }

        /// <summary>
        /// 修改图书封面
        /// </summary>
        /// <param name="entity"></param>
        public bool ModifyImg(ProductModel entity)
        {
            commandText = @"UPDATE Product SET Image=@Image WHERE BookID=@BookID";
            var command = DbMain.GetSqlStringCommand(commandText);
            DbMain.AddInParameter(command, "BookID", DbType.Int32, entity.BookID);
            DbMain.AddInParameter(command, "Image", DbType.AnsiString, entity.Image);

            return (int)DbMain.ExecuteNonQuery(command) == 1;
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <returns></returns>
        public List<ProductModel> Search(ProductParam param)
        {
            var list = new List<Model.ProductModel>();
            DbCommand dbCommand = DbMain.GetStoredProcCommand("ProductPageSearch");
            dbCommand.CommandTimeout = 600;
            DbMain.AddInParameter(dbCommand, "Page", DbType.Int32, param.PageNo);
            DbMain.AddInParameter(dbCommand, "Rows", DbType.Int32, param.PageRow);
            DbMain.AddInParameter(dbCommand, "PublisherID", DbType.Int32, param.PublisherID);
            DbMain.AddInParameter(dbCommand, "CategoryIDBig", DbType.Int32, param.CategoryIDBig);
            DbMain.AddInParameter(dbCommand, "CategoryID", DbType.Int32, param.CategoryID);
            DbMain.AddInParameter(dbCommand, "BookName", DbType.Int32, param.BookName);
            DbMain.AddInParameter(dbCommand, "isSpecial", DbType.Int32, param.isSpecial);
            using (var dr = DbMain.ExecuteReader(dbCommand))
            {
                while (dr.Read())
                {
                    var m = new Model.ProductModel();
                    m = ConvertReaderToEntity(dr);
                    m.PublisherName = dr["PublisherName"].ToString();
                    m.CategoryName = dr["CategoryName"].ToString();
                    list.Add(m);
                }
            }
            return list;
        }

        /// <summary>
        /// 查询数量
        /// </summary>
        /// <returns></returns>
        public int SearchCount(ProductParam param)
        {
            DbCommand dbCommand = DbMain.GetStoredProcCommand("ProductPageCount");
            dbCommand.CommandTimeout = 600;
            DbMain.AddInParameter(dbCommand, "PublisherID", DbType.Int32, param.PublisherID);
            DbMain.AddInParameter(dbCommand, "CategoryIDBig", DbType.Int32, param.CategoryIDBig);
            DbMain.AddInParameter(dbCommand, "CategoryID", DbType.Int32, param.CategoryID);
            DbMain.AddInParameter(dbCommand, "BookName", DbType.Int32, param.BookName);
            DbMain.AddInParameter(dbCommand, "isSpecial", DbType.Int32, param.isSpecial);
            using (var dr = DbMain.ExecuteReader(dbCommand))
            {
                while (dr.Read())
                {
                    return Convert.ToInt32(dr["Num"]);
                }
            }
            return 0;
        }

        /// <summary>
        /// 热售产品
        /// </summary>
        /// <param name="Top">Top</param>
        /// <param name="CateID">分类</param>
        /// <returns></returns>
        public List<ProductModel> GetSaleHot(int Top, int CateID)
        {
            var list = new List<Model.ProductModel>();
            commandText = "SELECT TOP " + Top + "  p.*  FROM  Product p(NOLOCK) WHERE 1=1 ";
            if (CateID != 0)
            {
                commandText += " AND p.CategoryID=@CateID ";
            }
            commandText += " ORDER BY p.SaleNum DESC";
            var dbCommand = DbMain.GetSqlStringCommand(commandText);
            if (CateID != 0)
            {
                DbMain.AddInParameter(dbCommand, "CateID", DbType.Int32, CateID);
            }
            using (var dr = DbMain.ExecuteReader(dbCommand))
            {
                while (dr.Read())
                {
                    list.Add(ConvertReaderToEntity(dr));
                }
            }
            return list;
        }

        /// <summary>
        /// 获取总数
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public int GetCount(Model.ProductParam param)
        {
            string commandText = "SELECT COUNT(1) FROM Product p(NOLOCK) WHERE 1=1";
            var command = DbMain.GetSqlStringCommand(commandText);
            return (int)DbMain.ExecuteScalar(command);
        }

        /// <summary>
        /// 获取订单实体
        /// </summary>
        /// <param name="bookID"></param>
        /// <returns></returns>
        public ProductModel GetEntityById(int bookID)
        {
            commandText = @"SELECT 
	                            p.*,
	                            p1.PublisherName,
	                            c.CategoryName
                            FROM 
	                            Product p(NOLOCK)
	                            INNER JOIN Publisher p1(NOLOCK) ON p1.PublisherID=p.PublisherId
	                            INNER JOIN Category c(NOLOCK) ON c.CategoryID=p.CategoryID
                            WHERE
	                            1=1
	                            AND p.BookID=@BookID";
            var command = DbMain.GetSqlStringCommand(commandText);
            DbMain.AddInParameter(command, "BookID", DbType.Int32, bookID);
            using (var dr = DbMain.ExecuteReader(command))
            {
                if (dr.Read())
                {
                    var m = new Model.ProductModel();
                    m = ConvertReaderToEntity(dr);
                    m.PublisherName = dr["PublisherName"].ToString();
                    m.CategoryName = dr["CategoryName"].ToString();
                    return m;
                }
            }
            return null;
        }

        /// <summary>
        /// 转换实体
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        private static ProductModel ConvertReaderToEntity(IDataRecord dr)
        {
            return new ProductModel
            {
                BookID = (int)dr["BookID"],
                PublisherId = (int)dr["PublisherId"],
                Author = dr["Author"].ToString(),
                ISBN = dr["ISBN"].ToString(),
                SaleNum = (int)dr["SaleNum"],
                BookName = dr["BookName"].ToString(),
                Image = dr["Image"].ToString(),
                Recommend = dr["Recommend"].ToString(),
                Introduction = dr["Introduction"].ToString(),
                Directory = dr["Directory"].ToString(),
                PublishDate = Convert.ToDateTime(dr["PublishDate"]),
                CategoryID = (int)dr["CategoryID"],
                Price = (decimal)dr["Price"],
                TruePrice = (decimal)dr["TruePrice"],
                AddDate = Convert.ToDateTime(dr["AddDate"]),
                isSpecial = (int)dr["isSpecial"],
                CostPrice = (decimal)dr["CostPrice"]
            };
        }

        /// <summary>
        /// 是否存在
        /// </summary>
        /// <param name="Str">关键字</param>
        /// <returns></returns>
        public bool Exist(string Str)
        {
            commandText = "SELECT COUNT(1) FROM Product p(NOLOCK) WHERE p.BookName=@BookName";
            var command = DbMain.GetSqlStringCommand(commandText.ToString());
            DbMain.AddInParameter(command, "BookName", DbType.String, Str);
            return (int)DbMain.ExecuteScalar(command) > 0;
        }
    }

}